﻿using System;
using System.Collections.Generic;
using System.Data;
using BSF.Db;
using HuntingFishGame.Domain.Model;

namespace HuntingFishGame.Domain.DAL.User
{
    public class WxCodeDal
    {
        public List<WxCodeModel> SelectList(DbConn pubConn, string openId, int pageIndex, int pageSize, out int count)
        {
            try
            {
                int _count = 0;
                var models = SqlHelper.Visit(ps =>
                {
                    string strWhere = " where 1=1 ";
                    if (!string.IsNullOrWhiteSpace(openId))
                    {
                        strWhere = string.Concat(strWhere, " and c.opid=@opid");
                        ps.Add("@opid", openId);
                    }
                    string totalsql = string.Concat("select count(*) from TWx_Code c", strWhere);

                    _count = (int)pubConn.ExecuteScalar(totalsql, ps.ToParameters());

                    Int32 startIndex = (pageIndex - 1) * pageSize + 1;
                    Int32 endIndex = pageIndex * pageSize;
                    string order = "desc";
                    string sql = String.Concat(
                        " SELECT *  FROM ",
                        " (SELECT row_number() over(order by codeTime ", order, ") as rownum, ",
                        "c.*,ISNULL(u.UserName,'') UserName FROM TWx_Code c LEFT JOIN TUsers u ON c.userid=u.UserID",
                        strWhere, ")",
                        " as TEMP ",
                        " WHERE rownum between ", startIndex, " and ", endIndex
                    );
                    DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                    List<WxCodeModel> modelList = new List<WxCodeModel>();
                    foreach (DataRow dr in table.Rows)
                    {
                        modelList.Add(WxCodeModel.CreateModel(dr));
                    }
                    return modelList;
                });
                count = _count;
                return models;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

    }
}
